package com.bw.dao;

import com.bw.bean.AA;
import com.bw.bean.AB;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface AAdao {

    @Select("SELECT c2.name,sum(final_total_amount) value FROM order_info o \n" +
            "LEFT JOIN order_detail d ON d.order_id=o.id\n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN base_category3 c3 ON s.category3_id=c3.id \n" +
            "LEFT JOIN base_category2 c2 ON c3.category2_id=c2.id \n" +
            "GROUP BY c2.name ORDER BY value desc")
    List<AA> getlist1();

    @Select("SELECT u.name,sum(final_total_amount) value  FROM user_info u \n" +
            "LEFT JOIN order_info o on o.user_id=u.id \n" +
            "GROUP BY u.birthday ORDER BY value desc")
    List<AA> getlist2();

    @Select("SELECT yys name,sum(final_total_amount) value FROM order_info o WHERE yys is not null GROUP BY yys ORDER BY value desc")
    List<AA> getlist3();

    @Select("SELECT b.name,\n" +
            "if(u.gender,'男','女') sex,\n" +
            "sum(final_total_amount) value \n" +
            "FROM user_info u \n" +
            "LEFT JOIN order_info o ON o.user_id=u.id \n" +
            "LEFT JOIN order_detail d ON d.order_id=o.id\n" +
            "LEFT JOIN sku_info s ON d.sku_id=s.id \n" +
            "LEFT JOIN base_trademark b ON s.tm_id=b.id \n" +
            "WHERE b.name is not null\n" +
            "GROUP BY b.name ORDER BY value desc")
    List<AB> getlist4();

}
